﻿namespace QualityRpt.Dao.Db2
{
    using log4net;
    using Org.SqlHelper;
    using QualityRpt.Dao;
    using QualityRpt.Utils;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.Odbc;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Web;
    using System.Web.Caching;
    using System.IO;
    using System.Threading.Tasks;
    using System.Collections.Generic;

    class UNReadTransDao : IUNReadTransDao
    {
        const string cachePrefix = "UNTR-";    
        private static ILog logger = LogManager.GetLogger("UNReadTransDao");
        private static DateTime? UATime = null;
        private static string UAText = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\UA.txt";
        public void DeleteWithCommit(string tableName, string deleteCondition, int commitCount)
        {
            OdbcConnection connection;
            object obj2 = 0;
            using (connection = ODBCHelper.GetConnection())
            {
                obj2 = ODBCHelper.ExecuteScalar("SELECT COUNT(*) FROM " + tableName + " WHERE " + deleteCondition, new OdbcParameter[0]);
            }
            long num = Convert.ToInt64(obj2) + commitCount;
            while (num > 0)
            {
                using (connection = ODBCHelper.GetConnection())
                {
                    string cmdText = string.Concat(new object[] { "DELETE FROM (SELECT 1 FROM ", tableName, " WHERE ", deleteCondition, " FETCH FIRST ", commitCount, " ROWS ONLY)" });
                    ODBCHelper.ExecuteNonQuery(CommandType.Text, cmdText, new OdbcParameter[0]);
                    num -= commitCount;
                }
            }
        }

        public void Extract(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            StringBuilder builder = new StringBuilder();
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            this.DeleteWithCommit("RPT.UNREAD_TRANS_NOTE", strWhere, 1000);
			string strload = "LOAD FROM (SELECT MID,PLAZAID,LANENO,TRANSOCCURTIME,OBUNO,ICARD_ISSUERNUM,ICARD_NETID,PAYTYPE,TABLETYPE,1,0,'1' FROM RPT.TRANS_NOTE WHERE DATASOURCE=2 AND LANETYPE =0 AND " + strWhere + ") OF CURSOR INSERT INTO RPT.UNREAD_TRANS_NOTE(MID,PLAZAID,LANENO,TRANSOCCURTIME,OBU_NO,CARD_NO,ICARD_NETID,PAYTYPE,TABLETYPE,IS_UNREAD,IS_UNREAD_OBU,PROGRAMME) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            
            OdbcParameter loadparam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strload);
            logger.Info("execute "+strload);
            OdbcDataReader reader = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", new OdbcParameter[] { loadparam });
            reader.Read();
            builder.Append(string.Concat(new object[] { "机具交易数据:读取", reader.GetValue(0), ",忽略", reader.GetValue(1), ",装入", reader.GetValue(2), ",拒绝", reader.GetValue(3), ",删除", reader.GetValue(4), ",落实", reader.GetValue(5), ";" }));
            logger.Info("机具交易数据抽取完成-" + DateTime.Now.ToString() + ";" + builder.ToString());
			reader.Close ();
			DataTable accTable = ODBCHelper.ReadTable("SELECT MID,TRANSOCCURTIME,CARD_NO,PLAZAID FROM RPT.UNREAD_TRANS_NOTE WHERE "+strWhere);
            int uncnt = 0; int nouncnt = 0;
            List<Task<bool>> paraTasks = new List<Task<bool>>();
            for (int i = 0; i < accTable.Rows.Count; i++)
            {
                DataRow accRow = accTable.Rows[i];
                if (CurTaskCnt >= MaxTask)
                {
                    //等待多任务运算结束
                    Task.WaitAll(paraTasks.ToArray());//等待10个线程完成后
                    foreach (Task<bool> t in paraTasks) {
                        if (t.Result)
                        {
                            uncnt++;
                        }
                        else {
                            nouncnt++;
                        }
                    }
                    //重新开始并行运算
                    paraTasks.Clear();
                    CurTaskCnt = 0;//恢复
                    UATime = Convert.ToDateTime(accRow["TRANSOCCURTIME"]);
                    string unreadtxt = "当前漏读分析时间:" + UATime.ToString();
                    File.WriteAllText(UAText, unreadtxt);
                }
                Task<bool> task = new Task<bool>(()=>ParalleAnalysis(accRow));
                task.Start();
                paraTasks.Add(task);
                CurTaskCnt++;

                if (i % 200 == 0)
                {
                    int curIndex = i + 1;
                    string strDes = "分析了到了(" + curIndex.ToString() + "/" + accTable.Rows.Count + ")机具交易数据,漏读共" + uncnt + "条,非漏读共" + nouncnt + "条";
                    logger.Info(strDes);
                }
            }
            logger.Info(startTime.ToString("yyyy-MM-dd HH:mm:ss") + "至" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "漏读分析完成");
            
        }
        const int MaxTask = 40;//最大任务数
        int CurTaskCnt = 0;
        private bool ParalleAnalysis(DataRow accRow)
        {
            bool rt = true;
            try
            {
                DateTime accTranstime = DateTime.Parse(accRow["TRANSOCCURTIME"].ToString());
                DateTime accStarttime = accTranstime.AddSeconds(-255); DateTime accEndtime = accTranstime.AddSeconds(255);

                string onecheckSql = "SELECT TR.MID,TR.OBUNO,TR.TRANSOCCURTIME,TR.ICARD_ISSUERNUM FROM RPT.TRANS_NOTE AS TR WHERE TR.TRANSOCCURTIME>=? AND TR.TRANSOCCURTIME<=? "
                    + " AND TR.DATASOURCE=1 AND TR.OBUNO IN (SELECT OBU_NO FROM RPT.OBU_CARD_RELATION WHERE CARD_NO =?) AND (TR.PLAZAID=? OR TR.PLAZAID IN (SELECT AL.AJOIN_PLAZA_ID FROM RPT.AJOIN_LANE AS AL WHERE AL.MAIN_PLAZA_ID =?))";

                DataTable selTable = ODBCHelper.ReadTable(onecheckSql, new OdbcParameter[] { 
                        ODBCHelper.CreateInputParameter("START_TIME",OdbcType.DateTime,accStarttime),
                        ODBCHelper.CreateInputParameter("END_TIME",OdbcType.DateTime,accEndtime),
                        ODBCHelper.CreateInputParameter("CARD_NO",OdbcType.VarChar,accRow["CARD_NO"]),
                        ODBCHelper.CreateInputParameter("PLAZAID",OdbcType.Int,accRow["PLAZAID"]),
                        ODBCHelper.CreateInputParameter("MPLAZAID",OdbcType.Int,accRow["PLAZAID"])});
                if (selTable.Rows.Count > 0)
                {
                    rt = false;
                    DataRow selRow = selTable.Rows[0];
                    ODBCHelper.ExecuteNonQuery("UPDATE RPT.UNREAD_TRANS_NOTE SET IS_UNREAD =0 WHERE MID=?", new OdbcParameter[]{
                                    ODBCHelper.CreateInputParameter("MID",OdbcType.BigInt,accRow["MID"])});
                }
                else
                {
                    string obugetsql = "SELECT OBU_NO FROM RPT.OBU_CARD_RELATION WHERE CARD_NO = ?  ORDER BY LAST_TRANS_TIME DESC FETCH FIRST ROWS ONLY";
                    DataTable obugettable = ODBCHelper.ReadTable(obugetsql, new OdbcParameter[]{
                                ODBCHelper.CreateInputParameter("CARD_NO",OdbcType.VarChar,accRow["CARD_NO"])});
                    StringBuilder obuupsql = new StringBuilder("UPDATE RPT.UNREAD_TRANS_NOTE SET IS_UNREAD_OBU = 1 ");
                    string obugetno = string.Empty;
                    if (obugettable.Rows.Count > 0)
                    {
                        obuupsql.Append(",OBU_NO=? WHERE MID=?");
                        ODBCHelper.ExecuteNonQuery(obuupsql.ToString(), new OdbcParameter[]{
                                ODBCHelper.CreateInputParameter("OBU_NO",OdbcType.VarChar,obugettable.Rows[0]["OBU_NO"]),
                                ODBCHelper.CreateInputParameter("MID",OdbcType.BigInt,accRow["MID"])
                            });
                    }
                    else
                    {
                        obuupsql.Append(" WHERE MID=?");
                        ODBCHelper.ExecuteNonQuery(obuupsql.ToString(), new OdbcParameter[]{
                                ODBCHelper.CreateInputParameter("MID",OdbcType.BigInt,accRow["MID"])
                            });
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error("漏读分析错误.MID " + accRow["MID"].ToString() + ex);
            }
            return rt;
        }

        public void StatTransCount(DateTime startTime, DateTime endTime)
        {
            string deleteCondition = "SUM_DATE >='" + startTime.ToString("yyyy-MM-dd") + "' AND SUM_DATE<'" + endTime.ToString("yyyy-MM-dd") + "'";
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            this.DeleteWithCommit("RPT.UNREAD_TRANS_BY_DAY", deleteCondition, 1000);
            string strSql = "SELECT DATE(TRANSOCCURTIME) AS SUM_DATE,A.ROAD_ID,A.ROAD_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID AS CONTRACT_ID,C.CONTRACT_NO,A.AIRWIRE_SPEC_ID,A.AIRWIRE_SPEC_NAME"
                   +",C.SUPPLIER_ID,C.SUPPLIER_NAME,A.AIRWIRE_SUPPLIER_ID,A.AIRWIRE_SUPPLIER_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO,COUNT(MID) AS TRANS_COUNT,(CASE WHEN A.ROAD_ID IS NULL THEN 0 ELSE 1 END) AS IS_UNREAD,(CASE WHEN C.SPEC_ID IS NULL THEN 0 ELSE 1 END) AS IS_UNREAD_OBU " 
                   +" FROM RPT.UNREAD_TRANS_NOTE INNER JOIN RPT.DEVICE_CONTRACT_DETAIL AS C ON OBU_NO >= C.START_NO AND OBU_NO <= C.END_NO INNER JOIN RPT.AIRWIRE_PLAZA AS A ON PLAZAID = A.PLAZA_ID AND LANENO = A.LANE_NO "
                   +" WHERE "+strWhere+" AND C.DEVICE_ID=1"
                   +" GROUP BY DATE(TRANSOCCURTIME),A.ROAD_ID,A.ROAD_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID,C.CONTRACT_NO,A.AIRWIRE_SPEC_ID,A.AIRWIRE_SPEC_NAME,C.SUPPLIER_ID,C.SUPPLIER_NAME,A.AIRWIRE_SUPPLIER_ID,A.AIRWIRE_SUPPLIER_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO";
            DataTable table = ODBCHelper.ReadTable( strSql);
           
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                try
                {
                    ODBCHelper.ExecuteNonQuery(
                         "INSERT INTO RPT.UNREAD_TRANS_BY_DAY(SUM_DATE,ROAD_ID,ROAD_NAME,OBU_SPEC_ID,OBU_SPEC_NAME,OBU_CONTRACT_ID,OBU_CONTRACT_NO,AIRWIRE_SPEC_ID,AIRWIRE_SPEC_NAME" +
                        ",OBU_SUPPLIER_ID,OBU_SUPPLIER_NAME,AIRWIRE_SUPPLIER_ID,AIRWIRE_SUPPLIER_NAME,PLAZA_ID,PLAZA_NAME,LANE_NO,UNREAD_TRANS_COUNT,IS_UNREAD,IS_UNREAD_OBU)"
                        + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new OdbcParameter[] { 
                            ODBCHelper.CreateInputParameter("SUM_DATE", OdbcType.Date, row["SUM_DATE"]), 
                            ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]), 
                            ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]), 
                            ODBCHelper.CreateInputParameter("OBU_SPEC_ID", OdbcType.Int, row["SPEC_ID"]), 
                            ODBCHelper.CreateInputParameter("OBU_SPEC_NAME", OdbcType.VarChar, 200, row["SPEC_NAME"]), 
                            ODBCHelper.CreateInputParameter("OBU_CONTRACT_ID", OdbcType.Int, row["CONTRACT_ID"]), 
                            ODBCHelper.CreateInputParameter("OBU_CONTRACT_NO", OdbcType.VarChar, 200, row["CONTRACT_NO"]), 
                            ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_ID", OdbcType.Int, row["AIRWIRE_SPEC_ID"]), 
                            ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_NAME", OdbcType.VarChar, 200, row["AIRWIRE_SPEC_NAME"]), 
							ODBCHelper.CreateInputParameter("OBU_SUPPLIER_ID", OdbcType.Int, row["SUPPLIER_ID"]), 
                            ODBCHelper.CreateInputParameter("OBU_SUPPLIER_NAME", OdbcType.VarChar, 200, row["SUPPLIER_NAME"]), 
                            ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_ID", OdbcType.Int, row["AIRWIRE_SUPPLIER_ID"]), 
                            ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_NAME", OdbcType.VarChar, 200, row["AIRWIRE_SUPPLIER_NAME"]),
                            ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, row["PLAZA_ID"]), 
                            ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar, 200, row["PLAZA_NAME"]),
                            ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.SmallInt, row["LANE_NO"]), 
                            ODBCHelper.CreateInputParameter("UNREAD_TRANS_COUNT", OdbcType.Int, row["TRANS_COUNT"]), 
                            ODBCHelper.CreateInputParameter("IS_UNREAD", OdbcType.SmallInt, row["IS_UNREAD"]), 
                            ODBCHelper.CreateInputParameter("IS_UNREAD_OBU", OdbcType.SmallInt, row["IS_UNREAD_OBU"]) });
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
        }

        /// <summary>
        /// 漏读标签综合查询支持
        /// </summary>
        public DataTable GetOBUStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, string cnin,
            string rdin, string plazain, string lanein, bool fromTemp, string[] numstartArray, string[] numendArray)
        {
            DateTime trackStart = DateTime.Now;
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个获得拆卸标签个数的sql语句
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件和分组
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SPEC_NAME,");
                sbDSelect.Append("D.SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SPEC_ID IN (" + specin + ")");
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.CONTRACT_NO,");
                sbDSelect.Append("D.CONTRACT_NO,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
            }
			sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD_OBU = 1 ");
			if (numstartArray != null && numstartArray.Length > 0)
			{
				sbSWhere.Append(" AND (");
				for (int i = 0; i < numstartArray.Length; i++)
				{
					if (i > 0)
					{
						sbSWhere.Append(" OR ");
					}
					sbSWhere.Append("(TR.OBU_NO>='" + numstartArray[i] + "' AND TR.OBU_NO<='" + numendArray[i] + "')");
				}
				if (fromTemp)
				{
					sbSWhere.Append(" OR TR.OBU_NO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
				}
				sbSWhere.Append(")");
				sbDGroup.Append("U.OBUNO,");sbDSelect.Append("U.OBUNO,");
			}
            if (sbDGroup.Length < 1)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
            }
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }
            }
            
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            #endregion
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + "TR.OBU_NO AS OBUNO,SUM(1) AS UCOUNT FROM RPT.UNREAD_TRANS_NOTE AS TR ");
            if (sbSWhere.ToString().Contains("A."))
            {
                sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            }
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + "TR.OBU_NO) SELECT " + sbDSelect.ToString() 
                + " SUM(UCOUNT) AS TRANSCOUNT FROM U LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=U.OBUNO AND U.OBUNO<=D.END_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            string cachekey = cachePrefix + "-" + sbSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                rtTable = ODBCHelper.ReadTable(sbSql.ToString());
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString()+"耗时:"+tsConsume.TotalSeconds+"s");
            return rtTable;
        }
        /// <summary>
        /// 漏读天线综合查询支持
        /// </summary>
        public DataTable GetAIRStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, 
            string rdin, string plazain, string lanein)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.ROAD_NAME,");
                sbDSelect.Append("A.ROAD_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.ROAD_ID IN(" + rdin + ")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.AIRWIRE_SUPPLIER_NAME,");
                sbDSelect.Append("A.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.AIRWIRE_SUPPLIER_ID IN(" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.AIRWIRE_SPEC_NAME,");
                sbDSelect.Append("A.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.AIRWIRE_SPEC_ID IN(" + specin + ")");
            }
            if (sbDGroup.Length < 1)
            {
                sbDGroup.Append("A.ROAD_NAME,A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,");
                sbDSelect.Append("A.ROAD_NAME,A.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
            }
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }

            }
            sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD=1 ");
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.PLAZAID,TR.LANENO,SUM(1) AS TRCOUNT FROM RPT.UNREAD_TRANS_NOTE AS TR ");
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + " TR.PLAZAID,TR.LANENO) SELECT " + sbDSelect.ToString()
                + " SUM(U.TRCOUNT) AS TRANSCOUNT FROM U LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON U.PLAZAID=A.PLAZA_ID AND U.LANENO=A.LANE_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            string cacheKey = cachePrefix + sbSql.ToString();
            logger.Info(sbSql.ToString());
            if (CacheManager.Get(cacheKey) == null)
            {
                rtTable = ODBCHelper.ReadTable( sbSql.ToString());
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataTable GetOBUDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string cnin, string rdin,
            string plazain, string lanein, bool fromTempTable, string[] numstartArray, string[] numendArray, PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();//where sql
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD_OBU = 1 ");
            if (numstartArray != null && numstartArray.Length > 0)
            {
                sbWhere.Append(" AND (");
                for (int i = 0; i < numstartArray.Length; i++)
                {
                    if (i > 0)
                    {
                        sbWhere.Append(" OR ");
                    }
                    sbWhere.Append("(TR.OBU_NO>='" + numendArray[i] + "' AND TR.OBU_NO<='" + numendArray[i] + "')");
                }
                if (fromTempTable)
                {
                    sbWhere.Append(" OR TR.OBU_NO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
                }
                sbWhere.Append(")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SPEC_ID IN (" + specin + ")");
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            sbSql.Append("SELECT D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBU_NO AS OBUNO,TR.TRANSOCCURTIME,A.PLAZA_NAME,TR.LANENO FROM RPT.UNREAD_TRANS_NOTE AS TR LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=TR.OBU_NO AND TR.OBU_NO<=D.END_NO ");
            sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString());
            sbCountSql.Append("WITH WT AS ("+sbSql.ToString()+") SELECT COUNT(*) FROM WT");
            sbSql.Append(" ORDER BY TR.TRANSOCCURTIME ASC");
            string cacheKey = cachePrefix+sbSql.ToString();
            if (pu != null)
            {
                cacheKey = cacheKey + "-" + pu.Page;
                if (CacheManager.Get(cacheKey) == null)
                {
                    pu.ListSql = sbSql.ToString();
                    pu.TotalCountSql = sbCountSql.ToString();
                    rtTable = pu.GetDb2OdbcPageList();
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            else
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = ODBCHelper.ReadTable( sbSql.ToString());
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            return rtTable;
        }
        /// <summary>
        /// 获取以obu分组的交易明细 v1.2
        /// </summary>
        public DataTable GetOBUGDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string cnin
           , string rdin, string plazain, string lanein, bool fromTempTable, string[] numstartArray, string[] numendArray, PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();//where sql
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD_OBU = 1 ");
            if (numstartArray != null && numstartArray.Length > 0)
            {
                sbWhere.Append(" AND (");
                for (int i = 0; i < numstartArray.Length; i++)
                {
                    if (i > 0)
                    {
                        sbWhere.Append(" OR ");
                    }
                    sbWhere.Append("(TR.OBU_NO>='" + numendArray[i] + "' AND TR.OBU_NO<='" + numendArray[i] + "')");
                }
                if (fromTempTable)
                {
                    sbWhere.Append(" OR TR.OBU_NO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
                }
                sbWhere.Append(")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SPEC_ID IN (" + specin + ")");
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            sbSql.Append("SELECT D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBU_NO AS OBUNO,SUM(1) AS TRANSCOUNT FROM RPT.UNREAD_TRANS_NOTE AS TR INNER JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=TR.OBU_NO AND TR.OBU_NO<=D.END_NO ");
            sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString() + " GROUP BY D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBU_NO");
            sbCountSql.Append("WITH OBUG AS (" + sbSql.ToString() + ") SELECT COUNT(*) FROM OBUG");
            sbSql.Append(" ORDER BY D.SUPPLIER_NAME ASC");
            string cacheKey = cachePrefix + sbSql.ToString();
            if (pu == null)
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = ODBCHelper.ReadTable( sbSql.ToString());
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            else
            {
                pu.ListSql = sbSql.ToString(); pu.TotalCountSql = sbCountSql.ToString();
                rtTable = pu.GetDb2OdbcPageList();
                cacheKey = cacheKey + "-" + pu.Page.ToString();
                if (CacheManager.Get(cacheKey) == null)
                {
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                    pu.DB2OdbcExecutePage();
                }
            }
            return rtTable;
        }
        /// <summary>
        /// 支持多条件的天线交易明细统计
        /// </summary>
        public DataTable GetAIRDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string rdin, string plazain, string lanein,PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();//count sql
            StringBuilder sbWhere = new StringBuilder();//
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD=1");
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN(" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN(" + lanein + ")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.AIRWIRE_SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.AIRWIRE_SPEC_ID IN (" + specin + ")"); ;
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            sbSql.Append("SELECT A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,A.PLAZA_NAME,TR.LANENO,TR.OBU_NO AS OBUNO,TR.TRANSOCCURTIME FROM RPT.UNREAD_TRANS_NOTE AS TR LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString());
            sbCountSql.Append("WITH TW AS (" + sbSql.ToString() + ") SELECT COUNT(*) FROM TW");
            sbSql.Append(" ORDER BY TR.TRANSOCCURTIME ASC");
            string cacheKey = cachePrefix + sbSql.ToString();
            if (pu == null)
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = ODBCHelper.ReadTable(sbSql.ToString());
                    CacheManager.Put(cacheKey, rtTable);
                }
                else {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            else
            {
                pu.ListSql = sbSql.ToString();
                pu.TotalCountSql = sbCountSql.ToString();
                cacheKey = cacheKey + "-" + pu.Page;
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = pu.GetDb2OdbcPageList();
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                    pu.DB2OdbcExecutePage();
                }
            }
            return rtTable;
        }
        /// <summary>
        /// 获取以天线分组的明细
        /// </summary>
        public DataTable GetAIRGDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string rdin, string plazain, string lanein, PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();//count sql
            StringBuilder sbWhere = new StringBuilder();//
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD=1");
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN(" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN(" + lanein + ")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.AIRWIRE_SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.AIRWIRE_SPEC_ID IN (" + specin + ")"); ;
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            sbSql.Append("SELECT A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,TR.PLAZAID,A.PLAZA_NAME,TR.LANENO,SUM(1) AS TRANSCOUNT FROM RPT.UNREAD_TRANS_NOTE AS TR LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString() + " GROUP BY A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,TR.PLAZAID,A.PLAZA_NAME,TR.LANENO ");
            sbCountSql.Append("WITH WT AS("+sbSql.ToString()+") SELECT COUNT(*) FROM WT");
            sbSql.Append(" ORDER BY A.AIRWIRE_SUPPLIER_NAME ASC");
            string cacheKey = cachePrefix+sbSql.ToString();
            if (pu == null)
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = ODBCHelper.ReadTable(sbSql.ToString());
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            else {
                pu.ListSql = sbSql.ToString();
                pu.TotalCountSql = sbCountSql.ToString();
                cacheKey = cacheKey + "-" + pu.Page.ToString();
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = pu.GetDb2OdbcPageList();
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                    pu.DB2OdbcExecutePage();
                }
            }            
            return rtTable;
        }
        /// <summary>
        /// 从中间表汇总标签漏读数据 
        /// </summary>
        public DataTable GetOBUStatByMQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin,
          string cnin, string rdin, string plin, string lnin)
        {
            StringBuilder sbS = new StringBuilder();
            StringBuilder sbW = new StringBuilder();
            StringBuilder sbG = new StringBuilder();
            DataTable rtTable = null;
            sbW.Append("TR.SUM_DATE>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.SUM_DATE<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD_OBU=1 ");
            bool hasGroup = false;
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.OBU_SUPPLIER_ID IN (" + spin + ")");
                sbS.Append("TR.OBU_SUPPLIER_NAME AS SUPPLIER_NAME,");
                sbG.Append("TR.OBU_SUPPLIER_NAME,");
                hasGroup = true;
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.OBU_SPEC_ID IN (" + specin + ")");
                sbS.Append("TR.OBU_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.OBU_SPEC_NAME,");
                hasGroup = true;
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.OBU_CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
                sbS.Append("TR.OBU_CONTRACT_NO AS CONTRACT_NO,");
                sbG.Append("TR.OBU_CONTRACT_NO,");
                hasGroup = true;
            }
            if (!hasGroup)
            {
                sbS.Append("TR.OBU_SUPPLIER_NAME AS SUPPLIER_NAME,TR.OBU_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.OBU_SUPPLIER_NAME,TR.OBU_SPEC_NAME,");
            }
            //where
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.ROAD_ID IN (" + rdin + ")");
            }
            if (plin != null && plin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.PLAZA_ID IN (" + plin + ")");
            }
            if (lnin != null && lnin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.LANE_NO IN (" + lnin + ")");
            }
            //时间分组情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),");
                        break;
                    case 2://按周
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,WEEK_ISO(TR.SUM_DATE) AS STWEEK,");
                        sbG.Append("YEAR(TR.SUM_DATE),WEEK_ISO(TR.SUM_DATE),");
                        break;
                    case 3://按日
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,DAY(TR.SUM_DATE) AS STDAY,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),DAY(TR.SUM_DATE),");
                        break;
                }
            }
            sbS.Remove(sbS.Length - 1, 1); sbG.Remove(sbG.Length - 1, 1); 
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT " + sbS.ToString() + ",SUM(UNREAD_TRANS_COUNT) AS TRANSCOUNT FROM RPT.UNREAD_TRANS_BY_DAY AS TR WHERE " + sbW.ToString() + " GROUP BY " + sbG.ToString());
            string cacheKey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                rtTable = ODBCHelper.ReadTable( sbSql.ToString());
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        /// <summary>
        /// 从中间表汇总天线漏读数据
        /// </summary>
        public DataTable GetAIRStatByMQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin,
           string rdin, string plin, string lnin)
        {
            StringBuilder sbS = new StringBuilder();
            StringBuilder sbW = new StringBuilder();
            StringBuilder sbG = new StringBuilder();
            DataTable rtTable = null;
            sbW.Append("TR.SUM_DATE>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.SUM_DATE<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.IS_UNREAD=1 ");
            bool hasGroup = false;
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.AIRWIRE_SUPPLIER_ID IN (" + spin + ")");
                sbS.Append("TR.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,");
                sbG.Append("TR.AIRWIRE_SUPPLIER_NAME,");
                hasGroup = true;
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.AIRWIRE_SPEC_ID IN (" + specin + ")");
                sbS.Append("TR.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.AIRWIRE_SPEC_NAME,");
                hasGroup = true;
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.ROAD_ID IN (" + rdin + ")");
                sbS.Append("TR.ROAD_NAME,");
                sbG.Append("TR.ROAD_NAME,");
                hasGroup = true;
            }
            if (!hasGroup)
            {
                sbS.Append("TR.ROAD_NAME,TR.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,TR.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.ROAD_NAME,TR.AIRWIRE_SUPPLIER_NAME,TR.AIRWIRE_SPEC_NAME,");
            }
            //where
            
            if (plin != null && plin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.PLAZA_ID IN (" + plin + ")");
            }
            if (lnin != null && lnin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.LANE_NO IN (" + lnin + ")");
            }
            //时间分组情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),");
                        break;
                    case 2://按周
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,WEEK_ISO(TR.SUM_DATE) AS STWEEK,");
                        sbG.Append("YEAR(TR.SUM_DATE),WEEK_ISO(TR.SUM_DATE),");
                        break;
                    case 3://按日
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,DAY(TR.SUM_DATE) AS STDAY,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),DAY(TR.SUM_DATE),");
                        break;
                }
            }
            sbS.Remove(sbS.Length - 1, 1); sbG.Remove(sbG.Length - 1, 1); 
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT " + sbS.ToString() + ",SUM(UNREAD_TRANS_COUNT) AS TRANSCOUNT FROM RPT.UNREAD_TRANS_BY_DAY AS TR WHERE " + sbW.ToString() + " GROUP BY " + sbG.ToString());
            logger.Info(sbSql.ToString());
            string cacheKey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                rtTable = ODBCHelper.ReadTable(sbSql.ToString());
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
		/// <summary>
		/// 获得军车统计信息
		/// </summary>
		public DataTable GetJZTBG (DateTime startTime, DateTime endTime){
			DataTable rtTable = null;
			string cacheKey = cachePrefix+"JZTBG-"+startTime.Ticks+"-"+endTime.Ticks;
			if (CacheManager.Get (cacheKey) == null) {
				StringBuilder sbSql = new StringBuilder ();
				sbSql.Append ("SELECT TABLETYPE,COUNT(1) AS TRANS_COUNT FROM RPT.UNREAD_TRANS_NOTE WHERE TRANSOCCURTIME>='");
				sbSql.Append (startTime.ToString ("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString ("yyyy-MM-dd HH:mm:ss") + "'");
				sbSql.Append (" AND (ICARD_NETID = '0501' OR PAYTYPE = 3) AND IS_UNREAD = 1 GROUP BY TABLETYPE");
				using (OdbcConnection conn = ODBCHelper.GetConnection ()) {
					rtTable = ODBCHelper.ReadTable (conn, CommandType.Text, sbSql.ToString ());
				}
				CacheManager.Put (cacheKey, rtTable);
			} else {
				rtTable = CacheManager.Get (cacheKey) as DataTable;
			}
			return rtTable;
		}
    }
}

